package cn.calm.swing.osgi.persistence.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import cn.calm.swing.osgi.api.ProjectPersistenceService;
import cn.calm.swing.osgi.api.entity.Project;
import cn.calm.swing.osgi.api.entity.ProjectProperty;

public class ProjectPersistenceServiceImpl implements ProjectPersistenceService {
//	private Map<Class<?>, Map<Serializable, Object>> cache = new HashMap<Class<?>, Map<Serializable, Object>>();
	static {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<Project> listAll() {
		List<Project> list = listByParameter(null);
//		cache.clear();
		return list;
	}

	@Override
	public List<Project> listAllByProjectType(String type) {
		Map<String, Serializable> para = new HashMap<String, Serializable>();
		para.put("type", type);
		List<Project> list = listByParameter(para);
		return list;
	}
	
	private List<Project> listByParameter(Map<String, Serializable> para) {
		Connection con = getConnection();
		List<Project> list = new ArrayList<Project>();
		try {
			StringBuilder sql = new StringBuilder("SELECT * FROM PROJECT");
			if (para != null && !para.isEmpty()) {
				sql.append(" WHERE 1 = 1 ");
				for (String s : para.keySet()) {
					sql.append(" AND ");
					sql.append(s);
					sql.append(" = ?");
				}
			}
			PreparedStatement createStatement = con.prepareStatement(sql
					.toString());
			int i = 1;
			if (para != null && !para.isEmpty()) {
				for (Serializable s : para.values()) {
					createStatement.setObject(i++, s);
				}
			}
			ResultSet rs = createStatement.executeQuery();
			while (rs.next()) {
				Project pro = new Project();
				pro.setId(rs.getLong("id"));
				pro.setName(rs.getString("name"));
				pro.setType(rs.getString("type"));
//				processCache(Project.class, pro.getId(), pro);
				processSelectProperties(pro, con);
				list.add(pro);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} finally {
			colseConnection(con);
		}
		return list;
	}

	private void processSelectProperties(Project pro, Connection con) {
		List<ProjectProperty> list = new ArrayList<ProjectProperty>();
		try {
			String sql = "SELECT * FROM PROJECT_PROPERTY WHERE PROJECT_ID = '"+pro.getId()+"'";
			PreparedStatement createStatement = con.prepareStatement(sql);
			ResultSet rs = createStatement.executeQuery();
			while (rs.next()) {
				ProjectProperty proper = new ProjectProperty();
				proper.setKey(rs.getString("KEY"));
				proper.setProperty(rs.getString("PROPERTY"));
				proper.setPropertyValue(rs.getString("PROPERTY_VALUE"));
				proper.setProjectId(rs.getLong("PROJECT_ID"));
				proper.setProject(pro);
				list.add(proper);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		}
		pro.setProperties(list);
	}

	@Override
	public void add(Project project) {
		project.setId(getProjectNextId());
		List<String> sqls = new ArrayList<String>();
		String sql = "INSERT INTO PROJECT ( ID , NAME , TYPE ) VALUES( '"
				+ project.getId() + "','" + project.getName() + "','"
				+ project.getType() + "')";
		sqls.add(sql);
		sqls.addAll(getPropertiesInsertSql(project.getProperties()));
		executeBatch(sqls);
	}

	public void executeBatch(List<String> entitySql) {
		Connection con = getConnection();
		try {
			con.setAutoCommit(false);
			Statement createStatement = con.createStatement();
			for (String s : entitySql) {
				createStatement.addBatch(s);
				System.out.println(s);
			}
			int[] executeBatch = createStatement.executeBatch();
			for(int i:executeBatch){
				System.out.println(i);
			}
			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			colseConnection(con);
		}
	}

	private Set<String> getPropertiesInsertSql(List<ProjectProperty> properties) {
		Set<String> sqls = new HashSet<String>();
		if (properties != null) {
			for (ProjectProperty pp : properties) {
				String sql = "INSERT INTO PROJECT_PROPERTY ( KEY ,PROPERTY , PROPERTY_VALUE , PROJECT_ID ) VALUES( '"
						+ pp.getKey()
						+ "','"
						+ pp.getProperty()
						+ "','"
						+ pp.getPropertyValue()
						+ "','"
						+ pp.getProject().getId() + "')";
				sqls.add(sql);
			}
		}
		return sqls;
	}

	@Override
	public void delete(Project project) {
		List<String> sqls = new ArrayList<String>();
		String sql = "DELETE * FROM PROJECT WHERE ID =" + project.getId();
		sqls.add(sql);
		sql = "DELETE * FROM PROJECT_PROPERTY WHERE PROJECT_ID ='"
				+ project.getId() + "'";
		sqls.add(sql);
		executeBatch(sqls);
	}

	@Override
	public void update(Project project) {
		List<String> sqls = new ArrayList<String>();
		String sql = "UPDATE PROJECT SET NAME='" + project.getName()
				+ "',TYPE='" + project.getType() + "' WHERE ID ="
				+ project.getId() ;
		sqls.add(sql);
		sql = "DELETE * FROM PROJECT_PROPERTY WHERE PROJECT_ID ='"
				+ project.getId() + "'";
		sqls.add(sql);
		sqls.addAll(getPropertiesInsertSql(project.getProperties()));
		executeBatch(sqls);
	}

	@Override
	public Project getById(Serializable id) {
		Map<String, Serializable> para = new HashMap<String, Serializable>();
		para.put("id", id);
		List<Project> list = listByParameter(para);
		if (list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	private Connection getConnection() {
		Connection con = null;
		try {
			con = DriverManager
					.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=F:\\db.mdb");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}

	private Long getProjectNextId(){
		Connection con = getConnection();
		Long id = null;
		try {
			String sql = "SELECT MAX(ID) AS MAXID FROM PROJECT";

			PreparedStatement createStatement;
			try {
				createStatement = con.prepareStatement(sql);
				ResultSet rs = createStatement.executeQuery();
				
				if (rs.next()) {
					id=rs.getLong("maxId");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			if(id==null){
				id=0L;
			}
			id=id+1;
			return id;
		} finally {
			colseConnection(con);
		}
	}
	private void colseConnection(Connection con){
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
